MySQL(三)：详解Innodb锁机制

您所在的位置：网站首页 › waits and waits怎么读 › MySQL(三)：详解Innodb锁机制

MySQL(三)：详解Innodb锁机制

2024-06-05 16:07| 来源: 网络整理| 查看: 265

1、前言

可以通过检查Innodb_row_lock状态变量来分析行锁的争夺情况：

mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 1572 | | Innodb_row_lock_time_avg | 1572 | | Innodb_row_lock_time_max | 1572 | | Innodb_row_lock_waits | 1 | +-------------------------------+-------+

注：如果Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比较高，说明锁争用比较严重。

2、InnoDB的行锁模式及加锁方法 1）共享锁（读锁）

将数据变为只读形式，不能进行更新，即读取锁定。假如事务T对数据对象A加上读锁，则事务T可以读A 也能修改A；其他事务只能再对A加读锁，而不能加写锁，直到事务T释放A上的读锁。这样保证了其他事务可以读取A，但在T释放A上的读锁之前，不能对A做任何修改。

2）排他锁（写锁）

允许获取到排他锁的事务更新数据，阻止其他事务获取相同数据集的共享读锁和排他写锁，即写入锁定。假如事务T对数据对象A加上写锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

3）加锁方式

MYSQL Innodb引擎默认的修改数据语句：update、delete、insert都会自动给涉及到的数据加上排他锁。

select操作默认不会加任何锁类型，可以使用select … for update 加排他锁，使用select … lock in share mode 加共享锁。

所以加过排他锁的数据行，其他事务中是不能修改其数据的，也不能通过for update和lock in share mode锁的方式查询数据；但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

4）Innodb行锁实现方式

Innodb行锁是通过给索引上的索引项加锁来实现的，而Oracle是通过给数据块中对相应数据行加锁来实现的。

这也就意味着，只要有通过索引条件来检索数据，InnoDB才使用行级锁，否者InnoDB将使用表锁！

3、意向共享锁和意向排他锁

意向锁是InnoDB数据操作之前自动加的，不需要用户干预。

1）意向共享锁：

表示事务准备给数据行加入共享锁，即一个数据行在加共享锁之前必须要先取得该表的IS锁。

2）意向排他锁：

表示事务准备给数据行加入排他锁，即一个数据行在加排他锁之前必须要先取得该表的IX锁。

4、加锁情况案例

1. 创建有索引表：

create table table_with_index(id int,name varchar(10)) engine=innodb; alter table table_with_index add index id(id); insert into table_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

在这里插入图片描述

2. 创建无索引表：

create table table_no_index(id int,name varchar(10)) engine=innodb; insert into table_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

在这里插入图片描述注意：表中的ID不是主键，Innodb会自动生成一个6位的主键（隐藏字段）。

1）不走索引条件查询时，使用表锁

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

session1session2set autocommit=0select * from table_no_index where id = 1;set autocommit=0select * from table_no_index where id =2select * from table_no_index where id = 1 for updateselect * from table_no_index where id = 2 for update;

session1：对id=1记录行进行加锁在这里插入图片描述 session2：对id=2记录行加写锁读，由于table_no_index表没有索引且查询条件中不包含索引列，所以session1已经对表加了锁，session2需要等待锁释放。由于MySQL的innodb中，事务有超时设置，所以session2等待了一段时间session1释放锁，一直等不到，就抛出了事务超时异常。在这里插入图片描述

2）走索引条件查询时，使用行锁 session1session2set autocommit=0select * from table_with_index where id = 1;set autocommit=0select * from table_with_index where id =2select * from table_with_index where id = 1 for update;select * from table_with_index where id = 2 for update;

session1: 在这里插入图片描述 session2:

5、总结 InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

减少锁冲突和死锁的措施：

尽量使用索引访问数据，使加锁更精确，避免表锁出现，从而减少锁冲突的概率；给记录集显式加锁时，一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少发生死锁的概率；尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

【本文地址】

MySQL(三)：详解Innodb锁机制

MySQL(三)：详解Innodb锁机制

今日新闻

推荐新闻